using System;
using System.Collections.Generic;
using System.Text;
using DAL;
using DTO;
using System.Data;
using System.Data.OleDb;


namespace BLL
{
    public class sinhvienDAL
    {


        public List<SINHVIEN> GetAllsv()
        {
            SINHVIEN sv;
            KHOA khoa;
            List<SINHVIEN> list = new List<SINHVIEN>();
            DataProvider dataprovider = new DataProvider();
            DataTable datatable = new DataTable();
            datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN", null);
            int a = datatable.Rows.Count;
            int b = a;
            for (int i = 0; i < a; i++)
            {
                sv = new SINHVIEN();
                khoa = new KHOA();
                sv.MaSV = datatable.Rows[i]["MASV"].ToString();
                sv.Diachi = datatable.Rows[i]["DIACHI"].ToString();
                sv.Gioitinh = datatable.Rows[i]["GIOITINH"].ToString();
                sv.Makhoa= datatable.Rows[i]["MAKHOA"].ToString();
        
                sv.Ten = datatable.Rows[i]["HOTEN"].ToString();
                string ht = sv.Ten;
                string dc = sv.Diachi;
                string gt = sv.Gioitinh;
                string mk = sv.Makhoa;
                list.Add(sv);
            }
            return list;
        }
        public DataTable bang()
        {
            DataProvider dataprovider = new DataProvider();
            DataTable bang = new DataTable();
            bang = dataprovider.ExecuteSelectQuery("Select * from khoa", null);

            return bang;
        }


        public SINHVIEN Getsv(string masv)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@masv", OleDbType.Char);
            parameters[0].Value = masv;
            DataTable datatable = new DataTable();
            datatable = dataprovider.ExecuteSelectQuery("Select * from SINHVIEN where MASV = @masv", parameters);
            SINHVIEN sv = new SINHVIEN();
            if (datatable.Rows.Count != 0)
            {
                sv.MaSV = datatable.Rows[0]["MASV"].ToString();
                sv.Diachi = datatable.Rows[0]["DIACHI"].ToString();
                sv.Gioitinh = datatable.Rows[0]["GIOITINH"].ToString();
                sv.Makhoa = datatable.Rows[0]["MAKHOA"].ToString();
                sv.Ten = datatable.Rows[0]["HOTEN"].ToString();
                return sv;
            }
            else
                return null;
        }


        public bool Updatesv(string masv, string hoten, string masvcu,string diachi,string gioitinh,string makhoa)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[6];
            parameters[0] = new OleDbParameter("@masv", OleDbType.Char);
            parameters[0].Value = masv;
            parameters[1] = new OleDbParameter("@hoten", OleDbType.Char);
            parameters[1].Value = hoten;
            parameters[2] = new OleDbParameter("@diachi", OleDbType.Char);
            parameters[2].Value = diachi;
            parameters[3] = new OleDbParameter("@gioitinh", OleDbType.Char);
            parameters[3].Value = gioitinh;
            parameters[4] = new OleDbParameter("@makhoa", OleDbType.Char);
            parameters[4].Value = makhoa;
            parameters[5] = new OleDbParameter("@masvcu", OleDbType.Char);
            parameters[5].Value = masvcu;
            return dataprovider.ExecuteNonSelectQuery("update SINHVIEN set MASV=@masv,MAKHOA=@makhoa, HOTEN=@hoten, GIOITINH=@gioitinh,DIACHI=@diachi where MASV = @masvcu", parameters);
        }
        public bool insersv(string masv, string hoten, string diachi, string gioitinh, string makhoa)
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[5];
            parameters[0] = new OleDbParameter("@masv", OleDbType.Char);
            parameters[0].Value = masv;
            parameters[2] = new OleDbParameter("@hoten", OleDbType.Char);
            parameters[2].Value = hoten;
            parameters[4] = new OleDbParameter("@diachi", OleDbType.Char);
            parameters[4].Value = diachi;
            parameters[3] = new OleDbParameter("@gioitinh", OleDbType.Char);
            parameters[3].Value = gioitinh;
            parameters[1] = new OleDbParameter("@makhoa", OleDbType.Char);
            parameters[1].Value = makhoa;
            
            return dataprovider.ExecuteNonSelectQuery("insert into SINHVIEN (MASV,MAKHOA,HOTEN,GIOITINH,DIACHI) values (@masv,@makhoa,@hoten,@gioitinh,@diachi)", parameters);
        }
        public bool xoasv(string masv )
        {
            DataProvider dataprovider = new DataProvider();
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@masv", OleDbType.Char);
            parameters[0].Value = masv;
            return dataprovider.ExecuteNonSelectQuery("delete from  sinhvien where Masv=@masv", parameters);
        }
        public int kiemtratk(string taikhoan,string matkhau)
           
        {
            DataProvider dataprovider = new DataProvider();
            DataTable bang = new DataTable();
            OleDbParameter[] parameters = new OleDbParameter[2];
            parameters[0] = new OleDbParameter("@taikhoan", OleDbType.Char);
            parameters[0].Value = taikhoan;
            parameters[1] = new OleDbParameter("@matkhau", OleDbType.Char);
            parameters[1].Value = matkhau;
            bang = dataprovider.ExecuteSelectQuery("select * from DANGNHAP where TAIKHOAN=@taikhoan and MATKHAU=@matkhau", parameters);
            int a = bang.Rows.Count;
            return a;
        }
        public List<SINHVIEN> tiemsv(string masv, string hoten,  string makhoa)
        {
            SINHVIEN sv;
            List<SINHVIEN> list = new List<SINHVIEN>();
            DataProvider dataprovider = new DataProvider();
            DataTable datatable = new DataTable();
            OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@masv", OleDbType.Char);
            parameters[0].Value = masv;                
            
            parameters[1] = new OleDbParameter("@makhoa", OleDbType.Char);
            parameters[1].Value = makhoa;
            parameters[2] = new OleDbParameter("@hoten", OleDbType.Char);
            parameters[2].Value = hoten;
            datatable = dataprovider.ExecuteSelectQuery("Select MASV,MAKHOA,HOTEN,GIOITINH,DIACHI from SINHVIEN where MASV like '%'+@masv+'%' and MAKHOA like '%'+@makhoa+'%' and HOTEN like '%'+@hoten+'%' ", parameters);
            int a = datatable.Rows.Count;
            int b = a;
            for (int i = 0; i < a; i++)
            {
                sv = new SINHVIEN();
                sv.MaSV = datatable.Rows[i]["MASV"].ToString();
                sv.Diachi = datatable.Rows[i]["DIACHI"].ToString();
                sv.Gioitinh = datatable.Rows[i]["GIOITINH"].ToString();
                sv.Makhoa = datatable.Rows[i]["MAKHOA"].ToString();
                sv.Ten = datatable.Rows[i]["HOTEN"].ToString();
                string ht = sv.Ten;
                string dc = sv.Diachi;
                string gt = sv.Gioitinh;
                string mk = sv.Makhoa;
                list.Add(sv);
            }
            return list;
        }


    }
}
